import sqlalchemy
from sqlalchemy import create_engine, Column, Integer, String, Sequence, MetaData, Table, select
from unittest import TestCase

from sqlalchemy.orm import declarative_base, sessionmaker, Session

# 参考: https://docs.sqlalchemy.org/en/14/orm/tutorial.html
Base = declarative_base()


class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
    name = Column(String(50))
    fullname = Column(String(50))
    nickname = Column(String(50))

    def __repr__(self):
        return f"<User(name='{self.name}', fullname='{self.fullname}', nickname='{self.nickname}')>"


class TestVersion(TestCase):

    def setUp(self) -> None:
        self.engine = create_engine('sqlite:///:memory:', echo=True)

    def test_version(self):
        print(sqlalchemy.__version__)

    def test_connecting(self):
        result = self.engine.execute('select 1+1')
        print(result.first())

    def test_create_schema(self):
        print(User.__table__.__repr__())
        Base.metadata.create_all(self.engine)

    def test_create_instance(self):
        ed_user = User(name='ed', fullname='Ed Jones', nickname='edsnickname')
        print(ed_user)
        print(ed_user.id)

    def test_adding_and_updating_objects(self):
        Base.metadata.create_all(self.engine)
        session = Session(bind=self.engine)
        ed_user = User(name='ed', fullname='Ed Jones', nickname='edsnickname')
        session.add(ed_user)
        our_user = session.query(User).filter_by(name='ed').first()
        print(our_user)
        print(ed_user is our_user)
        session.add_all([
            User(name='wendy', fullname='Wendy Williams', nickname='windy'),
            User(name='mary', fullname='Mary Contrary', nickname='mary'),
            User(name='fred', fullname='Fred Flintstone', nickname='freddy')])
        ed_user.nickname = 'eddie'
        print(session.dirty)
        print(session.new)
        session.commit()
        print(ed_user.id)

    def test_meta_query(self):
        Base.metadata.create_all(self.engine)
        # 定义元数据
        metadata = MetaData(self.engine)
        # case1: 全量加载元数据
        # MetaData.reflect(metadata)
        # hospital_table = metadata.tables['hospital']
        # case2: 按表格加载元数据
        users_table = Table('users', metadata, autoload=True)
        query = select(users_table.c.name).paginate(page=int(1), pre_page=3)
        for item in self.engine.execute(query).fetchall():
            print(item[0])


